package com.china08.yxyapi.api.home.activity.lectureresult;

import static org.springframework.data.mongodb.core.aggregation.Aggregation.group;
import static org.springframework.data.mongodb.core.aggregation.Aggregation.match;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.aggregation.TypedAggregation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.http.MediaType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.china08.yxyapi.api.YxyApiBaseController;
import com.china08.yxyapi.entity.db.huodong.lecture.YxyHuodongLectureResultArea;
import com.china08.yxyapi.entity.db.huodong.lecture.YxyHuodongLectureResultSchool;
import com.china08.yxyapi.entity.db.huodong.lecture.YxyHuodongLectureResultTea;
import com.china08.yxyapi.entity.mg.lecture.YxyLecturePrePack;
import com.china08.yxyapi.repository.db.lectureresult.YxyHuodongLectureResultAreaRepository;
import com.china08.yxyapi.repository.db.lectureresult.YxyHuodongLectureResultSchoolRepository;
import com.china08.yxyapi.repository.db.lectureresult.YxyHuodongLectureResultTeaRepository;

/**
 * 
 * 哈尔滨备课包征集活动统计初始化
 *
 */

@CrossOrigin
@RestController
public class LectureResultInitController extends YxyApiBaseController {

    @Autowired
    private YxyHuodongLectureResultTeaRepository lectureResultTeaRepository;

    @Autowired
    private YxyHuodongLectureResultSchoolRepository lectureResultSchoolRepository;

    @Autowired
    private YxyHuodongLectureResultAreaRepository lectureResultAreaRepository;

    @Autowired
    private MongoTemplate mt;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final Logger LOGGER = LoggerFactory.getLogger(LectureResultInitController.class);

    @RequestMapping(value = "/api_yw/home/activity/lectureresult/init", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public String process() {
        // 教师统计
        // LocalDate dateStart = LocalDate.of(2017, 6, 12);
        // LocalDate dateEnd = LocalDate.of(2017, 9, 30);

        // DateTimeFormatter dateFormat =
        // DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        // LocalDateTime dateStart = LocalDateTime.parse("2017-06-12 00:00:00",
        // dateFormat);
        // LocalDateTime dateEnd = LocalDateTime.parse("2017-09-30 23:59:59",
        // dateFormat);
        long currentTimeMillis = System.currentTimeMillis();
        LOGGER.info("====================开始时间：" + currentTimeMillis);
        LocalDateTime dateStart = LocalDateTime.of(2017, 6, 12, 0, 0, 0);
        LocalDateTime dateEnd = LocalDateTime.of(2017, 9, 30, 23, 59, 59);

        Criteria criteria = Criteria.where("areaIdCity").is("230100").and("createdDate").gte(dateStart).lte(dateEnd)
                .and("share").is(1).and("status").is(1);

        String[] fields = new String[] { "userId", "userNick", "schoolId", "schoolNick", "areaIdProvince", "areaIdCity",
                "areaIdDistrict", "packCount" };

        TypedAggregation<YxyLecturePrePack> aggregation1 = Aggregation.newAggregation(YxyLecturePrePack.class,
                match(criteria),
                group("userId", "schoolId").count().as("packCount").first("userNick").as("userNick").first("schoolNick")
                        .as("schoolNick").first("areaIdProvince").as("areaIdProvince").first("areaIdCity")
                        .as("areaIdCity").first("areaIdDistrict").as("areaIdDistrict").first("userId").as("userId")
                        .first("schoolId").as("schoolId"),
                Aggregation.project(fields));
        AggregationResults<YxyLecturePrePackTemp> result = mt.aggregate(aggregation1, YxyLecturePrePackTemp.class);

        List<YxyLecturePrePackTemp> mappedResults = result.getMappedResults();

        // 哈尔滨市下所有的区
        Map<String, String> areaMap = getAreaMap();
        YxyHuodongLectureResultTea huodongLectureResultTea = null;
        List<YxyHuodongLectureResultTea> list = new ArrayList<YxyHuodongLectureResultTea>();
        if (CollectionUtils.isNotEmpty(mappedResults)) {
            for (YxyLecturePrePackTemp tempPack : mappedResults) {
                huodongLectureResultTea = lectureResultTeaRepository.findBySchoolIdAndTeaId(tempPack.getSchoolId(),
                        tempPack.getUserId());
                if (huodongLectureResultTea == null) {
                    huodongLectureResultTea = new YxyHuodongLectureResultTea();
                }
                huodongLectureResultTea.setAreaIdCity(tempPack.getAreaIdCity());
                huodongLectureResultTea.setAreaIdDistrict(tempPack.getAreaIdDistrict());
                huodongLectureResultTea.setAreaIdProvince(tempPack.getAreaIdProvince());
                huodongLectureResultTea.setAreaNameCity("哈尔滨市");
                huodongLectureResultTea.setAreaNameProvince("黑龙江省");
                huodongLectureResultTea.setAreaNameDistrict(areaMap.get(tempPack.getAreaIdDistrict()));
                huodongLectureResultTea.setPackCount(tempPack.getPackCount());
                huodongLectureResultTea.setSchoolId(tempPack.getSchoolId());
                huodongLectureResultTea.setSchoolNick(tempPack.getSchoolNick());
                huodongLectureResultTea.setTeaId(tempPack.getUserId());
                huodongLectureResultTea.setTeaName(tempPack.getUserNick());
                list.add(huodongLectureResultTea);
            }
            lectureResultTeaRepository.save(list);
        }
        long currentTimeMillis2 = System.currentTimeMillis();
        LOGGER.info("====================结束时间：" + (currentTimeMillis2 - currentTimeMillis / 1000));

        // 学校的统计
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append(" select SUM(pack_count) pack_count,school_id,school_nick, ");
        sBuffer.append(" COUNT(tea_id) tea_count,area_id_city,area_name_city, ");
        sBuffer.append(" area_id_district,area_name_district,area_id_province, ");
        sBuffer.append(" area_name_province from yxy_huodong.yxy_huodong_lecture_result_tea GROUP BY school_id");
        List<Map<String, Object>> mapSchList = jdbcTemplate.queryForList(sBuffer.toString());
        YxyHuodongLectureResultSchool lResultSchool = null;
        List<YxyHuodongLectureResultSchool> lSchools = new ArrayList<YxyHuodongLectureResultSchool>();
        if (CollectionUtils.isNotEmpty(mapSchList)) {
            for (Map<String, Object> map : mapSchList) {
                lResultSchool = lectureResultSchoolRepository.findBySchoolId((String) map.get("school_id"));
                if (lResultSchool == null) {
                    lResultSchool = new YxyHuodongLectureResultSchool();
                }
                lResultSchool.setSchoolId((String) map.get("school_id"));
                lResultSchool.setSchoolNick((String) map.get("school_nick"));
                lResultSchool.setPackCount(Long.parseLong(map.get("pack_count") + ""));
                lResultSchool.setTeaCount(Integer.parseInt(map.get("tea_count") + ""));
                lResultSchool.setAreaIdCity((String) map.get("area_id_city"));
                lResultSchool.setAreaIdDistrict((String) map.get("area_id_district"));
                lResultSchool.setAreaIdProvince((String) map.get("area_id_province"));
                lResultSchool.setAreaNameCity((String) map.get("area_name_city"));
                lResultSchool.setAreaNameProvince((String) map.get("area_name_province"));
                lResultSchool.setAreaNameDistrict((String) map.get("area_name_district"));
                lSchools.add(lResultSchool);
            }
            lectureResultSchoolRepository.save(lSchools);
        }

        // 区域的统计
        String sql = "SELECT SUM(pack_count) pack_count,COUNT(DISTINCT(tea_id)) tea_count,area_id_city,"
                + "area_name_city,area_id_district,area_name_district," + "area_id_province,area_name_province "
                + "FROM yxy_huodong.yxy_huodong_lecture_result_tea GROUP BY area_id_district";

        List<Map<String, Object>> mapAreaList = jdbcTemplate.queryForList(sql);
        YxyHuodongLectureResultArea lectureResultArea = null;
        List<YxyHuodongLectureResultArea> lAreas = new ArrayList<YxyHuodongLectureResultArea>();
        if (CollectionUtils.isNotEmpty(mapAreaList)) {
            for (Map<String, Object> map : mapAreaList) {
                lectureResultArea = lectureResultAreaRepository
                        .findByAreaIdDistrict((String) map.get("area_id_district"));
                if (lectureResultArea == null) {
                    lectureResultArea = new YxyHuodongLectureResultArea();
                }
                lectureResultArea.setPackCount(Long.parseLong(map.get("pack_count") + ""));
                lectureResultArea.setTeaCount(Integer.parseInt(map.get("tea_count") + ""));
                lectureResultArea.setAreaIdCity((String) map.get("area_id_city"));
                lectureResultArea.setAreaIdDistrict((String) map.get("area_id_district"));
                lectureResultArea.setAreaIdProvince((String) map.get("area_id_province"));
                lectureResultArea.setAreaNameCity((String) map.get("area_name_city"));
                lectureResultArea.setAreaNameProvince((String) map.get("area_name_province"));
                lectureResultArea.setAreaNameDistrict((String) map.get("area_name_district"));
                lAreas.add(lectureResultArea);
            }
            lectureResultAreaRepository.save(lAreas);
        }
        return "初始化哈尔滨备课包征集活动ok!!";
    }

    public Map<String, String> getAreaMap() {
        Map<String, String> areaMap = new HashMap<String, String>();
        areaMap.put("230101", "市辖区");
        areaMap.put("230102", "道里区");
        areaMap.put("230103", "南岗区");
        areaMap.put("230104", "道外区");
        areaMap.put("230108", "平房区");
        areaMap.put("230109", "松北区");
        areaMap.put("230110", "香坊区");
        areaMap.put("230111", "呼兰区");
        areaMap.put("230112", "阿城区");
        areaMap.put("230123", "依兰县");
        areaMap.put("230124", "方正县");
        areaMap.put("230125", "宾县");
        areaMap.put("230126", "巴彦县");
        areaMap.put("230127", "木兰县");
        areaMap.put("230128", "通河县");
        areaMap.put("230129", "延寿县");
        areaMap.put("230182", "双城区");
        areaMap.put("230183", "尚志市");
        areaMap.put("230184", "五常市");
        return areaMap;
    }

    protected class YxyLecturePrePackTemp {

        private String userId;

        private String userNick;

        private String schoolId;

        private String schoolNick;

        private String areaIdProvince;

        private String areaIdCity;

        private String areaIdDistrict;

        private Long packCount;

        public String getAreaIdProvince() {
            return areaIdProvince;
        }

        public void setAreaIdProvince(String areaIdProvince) {
            this.areaIdProvince = areaIdProvince;
        }

        public String getAreaIdCity() {
            return areaIdCity;
        }

        public void setAreaIdCity(String areaIdCity) {
            this.areaIdCity = areaIdCity;
        }

        public String getAreaIdDistrict() {
            return areaIdDistrict;
        }

        public void setAreaIdDistrict(String areaIdDistrict) {
            this.areaIdDistrict = areaIdDistrict;
        }

        public String getUserId() {
            return userId;
        }

        public void setUserId(String userId) {
            this.userId = userId;
        }

        public String getUserNick() {
            return userNick;
        }

        public void setUserNick(String userNick) {
            this.userNick = userNick;
        }

        public String getSchoolId() {
            return schoolId;
        }

        public void setSchoolId(String schoolId) {
            this.schoolId = schoolId;
        }

        public String getSchoolNick() {
            return schoolNick;
        }

        public void setSchoolNick(String schoolNick) {
            this.schoolNick = schoolNick;
        }

        public Long getPackCount() {
            return packCount;
        }

        public void setPackCount(Long packCount) {
            this.packCount = packCount;
        }
    }

}
